﻿using Dapper;
using Domain.Entity.Basic_information;
using Domain.Entity.System_Carrier_ment;
using Domain.IRepository.System_Carrier_ment;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using System;
using System.Collections.Generic;
using System.Collections.Immutable;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Volo.Abp.DependencyInjection;

namespace EFCore.Repository.System_Carrier_ment
{
    [Dependency(ServiceLifetime.Scoped)]
    public class Carrier_mentRepository : ICarrier_mentRepository
    {
        private readonly MyDbContext myDbContext;

        public Carrier_mentRepository(MyDbContext myDbContext)
        {
            this.myDbContext = myDbContext;
        }

        public int Add(Carrier_ment carrier_Ment)
        {
            string sql = $@"INSERT [dbo].[Carrier_ment]([Cr_encode], [Cr_cp_name], [Cr_type], [Cr_bn_license_num], [Cr_bus_license_num], [Cr_tax_reg], 
                            [Cr_region], [Cr_address], [Cr_postcode], [Cr_state], [Cr_bn_license], [Cr_information], [Cr_name], 
                            [Cr_phone], [Cr_department], [Cr_posts], [Cr_email], [Cr_qq], [Cr_io_title], [Cr_tp_identification], 
                            [Cr_bank], [Cr_ao_name], [Cr_account], [Cr_contract], [Create_by], [Create_time], [Update_by], [Update_time], [Is_del], [Dv_num], [vc_num])
                            VALUES ('{carrier_Ment.Cr_encode}', '{carrier_Ment.Cr_cp_name}', '{carrier_Ment.Cr_type}', '{carrier_Ment.Cr_bn_license_num}', '{carrier_Ment.Cr_bus_license_num}', '{carrier_Ment.Cr_tax_reg}',
                            '{carrier_Ment.Cr_region}', '{carrier_Ment.Cr_address}', '{carrier_Ment.Cr_postcode}', '{carrier_Ment.Cr_state}', '{carrier_Ment.Cr_bn_license}', '{carrier_Ment.Cr_information}','{carrier_Ment.Cr_name}',
                            '{carrier_Ment.Cr_phone}', '{carrier_Ment.Cr_department}', '{carrier_Ment.Cr_posts}', '{carrier_Ment.Cr_email}', '{carrier_Ment.Cr_qq}', '{carrier_Ment.Cr_io_title}', '{carrier_Ment.Cr_tp_identification}',
                            '{carrier_Ment.Cr_bank}', '{carrier_Ment.Cr_ao_name}', '{carrier_Ment.Cr_account}', {carrier_Ment.Cr_contract}, '{carrier_Ment.Create_by}', '{carrier_Ment.Create_time}', '{carrier_Ment.Update_by}', 
                            '{carrier_Ment.Update_time}', '{carrier_Ment.Is_del}', '{carrier_Ment.Dv_num}', '{carrier_Ment.vc_num}')";
            return myDbContext.Database.GetDbConnection().Execute(sql);
        }

        public List<Carrier_ment> Carrier_Ments_()
        {
            string sql = $@"select * from [dbo].[Carrier_ment] where Is_del='0'";
            List<Carrier_ment> list=myDbContext.Database.GetDbConnection().Query<Carrier_ment>(sql).ToList();
            return list;
        }

        public int Count(string time, string Cr_cp_name)
        {
            string sql = $@"select * from [dbo].[Carrier_ment] where Is_del='0'";
            if(time!=null && time != "")
            {
                string[] time1 = time.Split(',');
                sql += $@" and Create_time>='{time1[0]}' and Create_time<='{time1[1]}'";
            }
            if(Cr_cp_name!=null && Cr_cp_name != "")
            {
                sql += $@" and Cr_cp_name like '%1%'";
            }
            //return myDbContext.carrier_Ments.Count();
            return myDbContext.Database.GetDbConnection().Query(sql).Count();
        }

        public int Delete(int Cr_id)
        {
            string sql = $@"update [dbo].[Carrier_ment] set Is_del='1' where Cr_id='{Cr_id}'";
            return myDbContext.Database.GetDbConnection().Execute(sql);
        }

        public List<Carrier_ment> GetCarrier_Ments(int currentPage, int PageSize)
        {
            string sql = @"select * from [dbo].[Carrier_ment] where Is_del='0'";
            List<Carrier_ment> list = myDbContext.Database.GetDbConnection().Query<Carrier_ment>(sql).ToList();
            foreach (var item in list)
            {
                string sql1 = $@"select Dv_id from [dbo].[Drivers_ment] as a, [dbo].[Carrier_ment] as b
                                 where a.Dv_carrier=b.Cr_id and [Dv_carrier]={item.Cr_id}";
                item.Dv_num = myDbContext.Database.GetDbConnection().Query(sql1).Count();
                List<Drivers_ment> drivers_Ments=myDbContext.Database.GetDbConnection().Query<Drivers_ment>(sql1).ToList();
                foreach(var item2 in drivers_Ments)
                {
                    string sql2 = $@"select * from [dbo].[Drivers_ment] as a
                                      inner join [dbo].[Vehicles_ment] as b on a.[Dv_id]=b.[Dv_id]
                                      where b.[Dv_id]='{item2.Dv_id}'";
                    item.vc_num += myDbContext.Database.GetDbConnection().Query(sql2).Count();
                }
            }
            var carrlist= list.Skip((currentPage - 1) * PageSize).Take(PageSize).ToList();
            return carrlist;
        }

        public List<Carrier_ment> GetCarrier_Ments_time(string time, string Cr_cp_name, int currentPage, int PageSize)
        {
            string sql = $@"select * from [dbo].[Carrier_ment] where Is_del='0'";
            if (time != null && time != "")
            {
                string[] time1 = time.Split(',');
                sql += $@" and Create_time>='{time1[0]}' and Create_time<='{time1[1]}'";
            }
            if (Cr_cp_name != null && Cr_cp_name != "")
            {
                sql += $@" and Cr_cp_name like '%{Cr_cp_name}%'";
            }
            List<Carrier_ment> list = myDbContext.Database.GetDbConnection().Query<Carrier_ment>(sql).ToList();
            foreach (var item in list)
            {
                string sql1 = $@"select Dv_id from [dbo].[Drivers_ment] as a, [dbo].[Carrier_ment] as b
                                 where a.Dv_carrier=b.Cr_id and [Dv_carrier]={item.Cr_id}";
                item.Dv_num = myDbContext.Database.GetDbConnection().Query(sql1).Count();
                List<Drivers_ment> drivers_Ments = myDbContext.Database.GetDbConnection().Query<Drivers_ment>(sql1).ToList();
                foreach (var item2 in drivers_Ments)
                {
                    string sql2 = $@"select * from [dbo].[Drivers_ment] as a
                                      inner join [dbo].[Vehicles_ment] as b on a.[Dv_id]=b.[Dv_id]
                                      where b.[Dv_id]='{item2.Dv_id}'";
                    item.vc_num += myDbContext.Database.GetDbConnection().Query(sql2).Count();
                }
            }
            var carrierlist = list.Skip((currentPage - 1) * PageSize).Take(PageSize).ToList();
            return carrierlist;
        }

        public List<Carrier_ment> GetCarrier_Ments_Update(int Cr_id)
        {
            string sql = $@"select * from [dbo].[Carrier_ment] where Cr_id='{Cr_id}'";
            return myDbContext.Database.GetDbConnection().Query<Carrier_ment>(sql).ToList();
        }

        public int Update(Carrier_ment carrier_ment)
        {
            string sql = $@"UPDATE [dbo].[Carrier_ment] SET [Cr_encode] = '{carrier_ment.Cr_encode}',[Cr_cp_name] ='{carrier_ment.Cr_cp_name}',[Cr_type] = '{carrier_ment.Cr_type}',[Cr_bn_license_num] = '{carrier_ment.Cr_bn_license_num}',[Cr_bus_license_num] = '{carrier_ment.Cr_bus_license_num}'
                            ,[Cr_tax_reg] = '{carrier_ment.Cr_tax_reg}',[Cr_region] = '{carrier_ment.Cr_region}',[Cr_address] = '{carrier_ment.Cr_address}',[Cr_postcode] = '{carrier_ment.Cr_postcode}',[Cr_state] = '{carrier_ment.Cr_state}',[Cr_bn_license] = '{carrier_ment.Cr_bn_license}',[Cr_information] = '{carrier_ment.Cr_information}'
                            ,[Cr_name] = '{carrier_ment.Cr_name}',[Cr_phone] = '{carrier_ment.Cr_phone}',[Cr_department] = '{carrier_ment.Cr_department}',[Cr_posts] = '{carrier_ment.Cr_posts}',[Cr_email] = '{carrier_ment.Cr_email}',[Cr_qq] = '{carrier_ment.Cr_qq}',[Cr_io_title] = '{carrier_ment.Cr_io_title}',[Cr_tp_identification] = '{carrier_ment.Cr_tp_identification}'
                            ,[Cr_bank] = '{carrier_ment.Cr_bank}',[Cr_ao_name] = '{carrier_ment.Cr_ao_name}',[Cr_account] = '{carrier_ment.Cr_account}',[Cr_contract] = '{carrier_ment.Cr_contract}',[Create_by] = '{carrier_ment.Create_by}',[Create_time] = '{carrier_ment.Create_time}'
                            ,[Update_by] = '{carrier_ment.Update_by}',[Update_time] = '{carrier_ment.Update_time}',[Is_del] = '{carrier_ment.Is_del}',[Dv_num] = '{carrier_ment.Dv_num}',[vc_num] = '{carrier_ment.vc_num}' WHERE Cr_id='{carrier_ment.Cr_id}'";
            return myDbContext.Database.GetDbConnection().Execute(sql);
        }
    }
}
